﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports CodeMatrix.Utilities
Imports System.Data.SqlClient
Imports CodeMatrix.Context
Imports System.Linq
Imports System.Collections.Generic
Imports CodeMatrix.Presentation

Namespace CodeMatrix.Business

    Public NotInheritable Class GenericData

        Public Shared Function GetStpDepartmentHirachy() As DataTable

            Dim level As Int32 = 0
            Dim srcTbl = CodeHelper.GetDataTable("exec [USP_GetDepartmentHirachy]; ", _
                                    New SqlParameter() {} _
            )
            Dim desTbl As DataTable = srcTbl.Clone()
            srcTbl.Columns("department_name").ReadOnly = False

            For Each i As DataRow In srcTbl.Select("department_parent is null")

                Dim iPid As Int32 = Convert.ToInt32(i("department_code"))

                ' ==========================================
                ' Copy row to destination table
                ' ==========================================
                desTbl.Rows.Add(i.ItemArray)

                ' ==========================================
                ' Remove source table
                ' ==========================================
                srcTbl.Rows.RemoveAt(srcTbl.Rows.IndexOf(i))

                ' ==========================================
                ' Recursive whole data
                ' ==========================================
                level = level + 1
                GetStpDepartmentHirachyNested(srcTbl, desTbl, iPid, level)
                level = level - 1
            Next

            Return desTbl

        End Function

        Public Shared Function GetStpDepartmentHirachy(ByVal master_department_level As Int32) As DataTable

            Dim level As Int32 = 0
            Dim srcTbl = CodeHelper.GetDataTable("exec [USP_GetDepartmentHirachy]; ", _
                                    New SqlParameter() {} _
            )
            Dim desTbl As DataTable = srcTbl.Clone()
            srcTbl.Columns("department_name").ReadOnly = False

            For Each i As DataRow In srcTbl.Select("department_parent is null")

                Dim iPid As Int32 = Convert.ToInt32(i("department_code"))

                ' ==========================================
                ' Copy row to destination table
                ' ==========================================
                desTbl.Rows.Add(i.ItemArray)

                ' ==========================================
                ' Remove source table
                ' ==========================================
                srcTbl.Rows.RemoveAt(srcTbl.Rows.IndexOf(i))

                ' ==========================================
                ' Recursive whole data
                ' ==========================================
                level = level + 1
                GetStpDepartmentHirachyNested(srcTbl, desTbl, iPid, level)
                level = level - 1
            Next

            desTbl.DefaultView.RowFilter = "master_department_level < " & master_department_level

            Return desTbl.DefaultView.ToTable()

        End Function

        Public Shared Function GetStpDepartmentByPrivilege() As DataTable

            Dim department_code = HttpContext.Current.Session("department_code")
            Dim level As Int32 = 0
            Dim srcTbl = CodeHelper.GetDataTable("exec [USP_GetDepartmentByPrivilege] @group_user_type ,@department_code   ", _
                                    New SqlParameter() { _
                                        New SqlParameter("@group_user_type", HttpContext.Current.Session("group_user_type")), _
                                        New SqlParameter("@department_code", department_code) _
                                    } _
            )
            Dim desTbl As DataTable = srcTbl.Clone()
            srcTbl.Columns("department_name").ReadOnly = False

            For Each i As DataRow In srcTbl.Select("department_parent is null or department_code = " & department_code)

                Dim iPid As Int32 = Convert.ToInt32(i("department_code"))

                ' ==========================================
                ' Copy row to destination table
                ' ==========================================
                desTbl.Rows.Add(i.ItemArray)

                ' ==========================================
                ' Remove source table
                ' ==========================================
                srcTbl.Rows.RemoveAt(srcTbl.Rows.IndexOf(i))

                ' ==========================================
                ' Recursive whole data
                ' ==========================================
                level = level + 1
                GetStpDepartmentHirachyNested(srcTbl, desTbl, iPid, level)
                level = level - 1
            Next

            Return desTbl

        End Function

        Public Shared Function GetStpDepartmentByPrivilege1(ByVal master_dept_level As Integer) As DataTable

            Dim department_code = HttpContext.Current.Session("department_code")
            Dim level As Int32 = 0
            Dim srcTbl = CodeHelper.GetDataTable("exec [USP_GetDepartmentByPrivilege1] @group_user_type ,@department_code ,@master_dept_level   ", _
                                    New SqlParameter() { _
                                        New SqlParameter("@group_user_type", HttpContext.Current.Session("group_user_type")), _
                                        New SqlParameter("@department_code", HttpContext.Current.Session("department_code")), _
                                        New SqlParameter("@master_dept_level", master_dept_level) _
                                    } _
            )
            Dim desTbl As DataTable = srcTbl.Clone()
            srcTbl.Columns("department_name").ReadOnly = False

            For Each i As DataRow In srcTbl.Select("department_parent is null or department_code = " & department_code)

                Dim iPid As Int32 = Convert.ToInt32(i("department_code"))

                ' ==========================================
                ' Copy row to destination table
                ' ==========================================
                desTbl.Rows.Add(i.ItemArray)

                ' ==========================================
                ' Remove source table
                ' ==========================================
                srcTbl.Rows.RemoveAt(srcTbl.Rows.IndexOf(i))

                ' ==========================================
                ' Recursive whole data
                ' ==========================================
                level = level + 1
                GetStpDepartmentHirachyNested(srcTbl, desTbl, iPid, level)
                level = level - 1
            Next

            Return desTbl

        End Function

        Public Shared Function GetStpDepartmentByPrivilege(ByVal group_user_type As Integer, ByVal department_code As Integer) As DataTable

            Dim desTbl = Nothing
            Dim level As Int32 = 0

            Dim srcTbl = CodeHelper.GetDataTable("exec [USP_GetDepartmentByPrivilege] @group_user_type ,@department_code   ", _
                                                New SqlParameter() { _
                                                    New SqlParameter("@group_user_type", group_user_type), _
                                                    New SqlParameter("@department_code", department_code) _
                                                } _
                        )
            desTbl = srcTbl.Clone()
            srcTbl.Columns("department_name").ReadOnly = False

            For Each i As DataRow In srcTbl.Select("department_parent is null or department_code = " & department_code)

                Dim iPid As Int32 = Convert.ToInt32(i("department_code"))

                ' ==========================================
                ' Copy row to destination table
                ' ==========================================
                desTbl.Rows.Add(i.ItemArray)

                ' ==========================================
                ' Remove source table
                ' ==========================================
                srcTbl.Rows.RemoveAt(srcTbl.Rows.IndexOf(i))

                ' ==========================================
                ' Recursive whole data
                ' ==========================================
                level = level + 1
                GetStpDepartmentHirachyNested(srcTbl, desTbl, iPid, level)
                level = level - 1
            Next

            Return desTbl

        End Function

        Private Shared Sub GetStpDepartmentHirachyNested(ByVal srcTbl As DataTable, ByVal desTbl As DataTable, ByVal pid As Int32, ByRef level As Int32)

            For Each i As DataRow In srcTbl.Select("department_parent = " & pid)

                Dim iPid As Int32 = Convert.ToInt32(i("department_code"))

                ' ==========================================
                ' Copy row to destination table
                ' ==========================================
                i("department_name") = GetPrefix(i("department_name"), level)
                desTbl.Rows.Add(i.ItemArray)

                ' ==========================================
                ' Remove source table
                ' ==========================================
                srcTbl.Rows.RemoveAt(srcTbl.Rows.IndexOf(i))

                ' ==========================================
                ' Recursive whole data
                ' ==========================================
                level = level + 1
                GetStpDepartmentHirachyNested(srcTbl, desTbl, iPid, level)
                level = level - 1
            Next

        End Sub

        Public Shared Function GetPrefix(ByVal displayText As String, ByVal level As Int32) As String
            Dim prefix As String = String.Empty
            prefix = prefix.PadLeft(level * 2, "-")
            Return prefix & displayText
        End Function

        Public Shared Sub GetMenu(ByVal rootMenu As MenuItemCollection)

            rootMenu.Clear()
            ' ========================================
            ' Get data
            ' ========================================
            Dim tbl As DataTable = CodeMatrix.Utilities.CodeHelper.GetDataTable("exec dbo.[USP_GetMenuByPriviledge] @staff_code", _
                                    New SqlParameter("@staff_code", HttpContext.Current.Session("staff_code")))


            ' ========================================
            ' Create menu 
            ' Modified by Boonyarit Yingvoragan
            ' Modified date 21/10/2553
            ' ========================================
            Dim root As DataRow() = tbl.Select("menu_level = 0")

            For A = 0 To root.Length - 1 Step 1

                ' Create root menu
                rootMenu.Add(New MenuItem(root(A)("menu_name"), root(A)("menu_code"), "", root(A)("url") + ""))

                Dim i As Int32
                Dim rowParent As DataRow() = tbl.Select("menu_level = 1 and menu_parent = " & root(A)("menu_code"))
                For i = 0 To rowParent.Length - 1 Step 1
                    ' Create root menu
                    rootMenu.Item(A).ChildItems.Add(New MenuItem(rowParent(i)("menu_name"), rowParent(i)("menu_code"), rowParent(i)("picture") + "", rowParent(i)("url") + ""))

                    Dim rowLevel1 As DataRow() = tbl.Select("menu_level = 2 and menu_parent = " & rowParent(i)("menu_code"))
                    Dim j As Int32

                    For j = 0 To rowLevel1.Length - 1 Step 1
                        ' Create menu level 1
                        ' Comment by Boonyarit on 4/4/2554.
                        ' rootMenu.Item(A).ChildItems.Item(i).ChildItems.Add(New MenuItem(rowLevel1(j)("menu_name"), rowLevel1(j)("menu_code"), rowLevel1(j)("picture") + "", IIf(rowLevel1(j)("system_type") = 2, "iframe.aspx?url=" & rowLevel1(j)("url") & "", rowLevel1(j)("url") & "")))
                        rootMenu.Item(A).ChildItems.Item(i).ChildItems.Add(New MenuItem(rowLevel1(j)("menu_name"), rowLevel1(j)("menu_code"), rowLevel1(j)("picture") + "", Nothing))

                        Dim rowLevel2 As DataRow() = tbl.Select("menu_level = 3 and menu_parent = " & rowLevel1(j)("menu_code"))
                        Dim k As Int32

                        For k = 0 To rowLevel2.Length - 1 Step 1
                            ' Create menu level 2
                            rootMenu.Item(A).ChildItems.Item(i).ChildItems.Item(j).ChildItems.Add(New MenuItem(rowLevel2(k)("menu_name"), rowLevel2(k)("menu_code"), rowLevel2(k)("picture") + "", IIf(rowLevel2(k)("system_type") = 2, "iframe.aspx?url=" & rowLevel1(j)("url") & rowLevel2(k)("url") & "", rowLevel2(k)("url") & "")))
                        Next
                    Next
                Next
            Next

        End Sub

        Private Shared Sub GetMenuMain(ByVal rootMenu As MenuItemCollection, ByVal tbl As DataTable)

            Dim cMenuSystemId As List(Of Int32) = New List(Of Int32)()
            Dim itemSystemMenu As MenuItem = New MenuItem()
            itemSystemMenu.Text = "ข้อมูลหลักของระบบ"
            rootMenu.Add(itemSystemMenu)

            Dim cMenuSystem = tbl.AsEnumerable().Where(Function(f) 1 = 1).GroupBy(Function(f) New With {.menu_system_code = f.Field(Of Int32)("menu_system_code"), _
                              .menu_system_name = f.Field(Of String)("menu_system_name")})

            For Each i In cMenuSystem

                Dim iMenu As MenuItem = New MenuItem() With {.Text = i.Key.menu_system_name, .Value = i.Key.menu_system_code}
                If Not cMenuSystemId.Contains(i.Key.menu_system_code) Then

                    cMenuSystemId.Add(i.Key.menu_system_code)
                    itemSystemMenu.ChildItems.Add(iMenu)

                    ' ========================================
                    ' Get menu for sub menu
                    ' ======================================== 
                    For Each j In tbl.AsEnumerable() _
                        .Where(Function(f) f.Field(Of Int32)("menu_system_code") = i.Key.menu_system_code) _
                        .GroupBy(Function(f) New With {.sub_menu_code = f.Field(Of Int32)("sub_menu_code"), _
                                                       .sub_menu_name = f.Field(Of System.String)("sub_menu_name"), _
                                                       .sub_menu_url = f.Field(Of System.String)("sub_menu_url"), _
                                                       .menu_system_code = f.Field(Of System.Int32)("menu_system_code")})

                        Dim jUrl = String.Empty
                        If j.Key.sub_menu_url.Contains("?Insert") Then

                            jUrl = j.Key.sub_menu_url.Replace("_list", "_fv")
                        Else
                            jUrl = j.Key.sub_menu_url

                        End If

                        Dim jMenu As MenuItem = New MenuItem() With {.Text = j.Key.sub_menu_name, _
                                                                     .Value = j.Key.sub_menu_code, _
                                                                     .NavigateUrl = jUrl}
                        iMenu.ChildItems.Add(jMenu)
                    Next
                End If
            Next

        End Sub

        Private Shared Sub GetMenuSystem(ByVal rootMenu As MenuItemCollection, ByVal tbl As DataTable)

            Dim mnuSystem As MenuItem = New MenuItem()
            'mnuSystem.Text = "ระบบ"
            'Kim comment and update 10/10/2010
            mnuSystem.Text = "ข้อมูลระบบปฏิบัติงาน"
            mnuSystem.NavigateUrl = "#"
            rootMenu.Add(mnuSystem)

            Dim staff_code As Int32 = HttpContext.Current.Session("staff_code")
            Dim group_user_code As Int32 = HttpContext.Current.Session("group_user_code")
            Dim department_code As Int32 = HttpContext.Current.Session("department_code")
            Dim group_user_type As Int32 = HttpContext.Current.Session("group_user_type")

            Dim ctx = New DataClassesDataContext
            Try

                ' =========================================
                ' To skip main system
                ' =========================================
                Dim menuSystemIds = ctx.stp_main_systems.Where(Function(f) f.main_system_type = 1) _
                .Select(Function(f) f.main_system_code)

                For Each i In ctx.USP_GetSystemListByUserPrivilege(staff_code, department_code, group_user_code, group_user_type) 

                    If menuSystemIds.Contains(i.main_system_code) Then

                        Continue For

                    End If

                    Dim iMnu = New MenuItem()
                    iMnu.Text = i.main_system_name
                    iMnu.NavigateUrl = String.Format("main_system_fv.aspx?Edit={0}&readonly=true", i.main_system_code)
                    mnuSystem.ChildItems.Add(iMnu)
                    'kim comment and assign 10/10/2010
                    'rootMenu.Add(iMnu)

                    For Each j In ctx.USP_GetSystemAndMenuListByUserPrivilege(staff_code, department_code, group_user_code, i.main_system_code)

                        Dim jMnu = New MenuItem()
                        jMnu.Text = j.menu_system_name
                        jMnu.NavigateUrl = j.menu_system_url
                        iMnu.ChildItems.Add(jMnu)

                        For Each k In ctx.USP_GetSystemAndMenuAndSubListByUserPrivilege(staff_code, department_code, group_user_code, i.main_system_code, j.menu_system_code)

                            Dim kMnu = New MenuItem()
                            kMnu.Text = k.sub_menu_name
                            kMnu.NavigateUrl = k.sub_menu_url
                            jMnu.ChildItems.Add(kMnu)

                        Next
                    Next
                Next
            Catch ex As Exception

            End Try

        End Sub

        Public Shared Function GetStatusLabel(ByVal statusFlag As String) As String

            Select Case statusFlag
                Case "1"
                    Return "ใช้งาน"
                Case "2"
                    Return "ไม่ใช้งาน"
                Case Else
                    Return "ไม่ระบุ"
            End Select

        End Function

        Public Shared Function GetAdminTypeLabel(ByVal adminTypeFlag As String) As String

            Select Case adminTypeFlag
                Case "1"
                    Return "ผู้ดูแลระบบส่วนกลาง"
                Case "2"
                    Return "ผู้ดูแลหน่วยงาน"
                Case "3"
                    Return "พนักงานปฏิบัติงาน"
                Case Else
                    Return "ไม่ระบุ"
            End Select

        End Function

        Public Shared Function GetPrefixLabel(ByVal prefixFlag As String) As String

            Select Case prefixFlag
                Case "1"
                    Return "นาย"
                Case "2"
                    Return "นาง"
                Case "3"
                    Return "นางสาว"
                Case "4"
                    Return "คุณ"
                Case Else
                    Return "ไม่ระบุ"
            End Select

        End Function

        Public Shared Function GetSystemTypeLabel(ByVal systemTypeFlag As String) As String

            Select Case systemTypeFlag
                Case "1"
                    Return "ข้อมูลหลักของระบบ"
                Case "2"
                    Return "ข้อมูลปฏิบัติงาน"
                Case Else
                    Return "ไม่ระบุ"
            End Select

        End Function

        Public Shared Function GetViewMode() As String

            If HttpContext.Current.Request.QueryString("Edit") Is Nothing Then
                Return "&readonly=true"
            Else
                Return String.Empty
            End If

        End Function

        Public Shared Function GetMasterDepartmentLabel(ByVal obj As Object, ByVal master_department_code As Int32) As String

            Dim master_department_name = String.Empty
            Dim ctx = New DataClassesDataContext
            Try
                master_department_name = ctx.master_departments.SingleOrDefault(Function(f) f.master_department_code = master_department_code).master_department_name

            Catch ex As Exception
                GenericHelper.ShowAlert(obj, ex)

            Finally
                ctx.Dispose()

            End Try

            Return master_department_name

        End Function

        Public Shared Function GetDepartmentLabel(ByVal obj As Object, ByVal department_code As System.DBNull) As String

            Return "-"

        End Function

        Public Shared Function GetDepartmentLabel(ByVal obj As Object, ByVal department_code As Int32) As String

            Dim department_name = String.Empty
            Dim ctx = New DataClassesDataContext
            Try
                department_name = ctx.stp_departments.SingleOrDefault(Function(f) f.department_code = department_code).department_name

            Catch ex As Exception
                GenericHelper.ShowAlert(obj, ex)

            Finally
                ctx.Dispose()

            End Try

            Return department_name

        End Function


        Public Shared Function GetGroupUserLabel(ByVal obj As Object, ByVal group_user_code As Int32) As String

            Dim group_user_name = String.Empty
            Dim ctx = New DataClassesDataContext
            Try
                group_user_name = ctx.group_users.SingleOrDefault(Function(f) f.group_user_code = group_user_code).group_user_name

            Catch ex As Exception
                GenericHelper.ShowAlert(obj, ex)

            Finally
                ctx.Dispose()

            End Try

            Return group_user_name

        End Function

        Public Shared Function GetStaffLabel(ByVal obj As Object, ByVal staff_code As Int32) As String

            Dim staff_name = String.Empty
            Dim ctx = New DataClassesDataContext
            Try
                staff_name = ctx.staffs.SingleOrDefault(Function(f) f.staff_code = staff_code).staff_firstname & _
                                " " & ctx.staffs.SingleOrDefault(Function(f) f.staff_code = staff_code).staff_lastname

            Catch ex As Exception
                GenericHelper.ShowAlert(obj, ex)

            Finally
                ctx.Dispose()

            End Try

            Return staff_name

        End Function

        Public Shared Function GetMainSystemNameLabel(ByVal obj As Object, ByVal main_system_code As Int32) As String

            Dim main_system_name = String.Empty
            Dim ctx = New DataClassesDataContext
            Try
                main_system_name = ctx.stp_main_systems.SingleOrDefault(Function(f) f.main_system_code = main_system_code).main_system_name

            Catch ex As Exception
                GenericHelper.ShowAlert(obj, ex)

            Finally
                ctx.Dispose()

            End Try

            Return main_system_name

        End Function

        Public Shared Function GetDepartmentHirachy(ByVal obj As Object, ByVal department_code As Int32?) As String

            Dim department_name = String.Empty
            Dim ctx = New DataClassesDataContext
            Try
                department_name = ctx.UFN_GetSTPDepartmentHirachy(department_code)

            Catch ex As Exception
                GenericHelper.ShowAlert(obj, ex)

            Finally
                ctx.Dispose()

            End Try

            Return department_name

        End Function

        Public Shared Function GetNewPassword(ByVal saltValue As String) As String
            Dim encryptedPassword_l1 As String = Convert.ToBase64String(New System.Text.UnicodeEncoding().GetBytes("P@ssw0rd"))
            Dim encryptedPassword_l2 As String = ""
            Dim passPhrase = saltValue.Substring(0, 8)

            Dim rijndaelKey As New CodeMatrix.Utilities.RijndaelEnhanced(passPhrase, saltValue, 4, 8, 128, "SHA1", saltValue)
            encryptedPassword_l2 = rijndaelKey.Encrypt(encryptedPassword_l1)

            Return encryptedPassword_l2
        End Function

        Public Shared Sub WriteLog(ByVal logDescription As String, ByVal logMenu As Int32, ByVal logType As String, ByVal logCreateBy As Int32)
            Using ctx As New DataClassesDataContext()
                Dim log As CodeMatrix.Entities.log_system = New CodeMatrix.Entities.log_system()
                log.create_datetime = Now
                log.log_description = logDescription
                log.log_menu = logMenu
                log.log_type = logType
                log.staff_code_create = logCreateBy
                log.staff_ip_address = HttpContext.Current.Request.UserHostAddress
                ctx.log_systems.InsertOnSubmit(log)
                ctx.SubmitChanges()
            End Using
        End Sub

        Public Shared Function IsOnline() As String
            Try
                Dim dt As DataTable = CodeMatrix.Utilities.CodeHelper.GetDataTable("exec USP_GetOnlineStaff @staff_code", _
                                        New SqlParameter("@staff_code", HttpContext.Current.Session("staff_code")))
                If dt.Rows.Count > 0 Then
                    Dim row As DataRow = dt.Rows(0)
                    If DateTime.Now.AddMinutes(-20) <= Convert.ToDateTime(row("last_update_datetime")) Then
                        ' You are online
                        ' Make sure this is your session
                        If row("session_id") <> HttpContext.Current.Session("session_id") Then
                            Return row("ip_address")
                        Else
                            If row("ip_address") = HttpContext.Current.Request.UserHostAddress Then
                                ' Update current online session if you are login at the same computer
                                CodeMatrix.Utilities.CodeHelper.ExecuteNonQuery("exec USP_UpdateOnlineStaff @staff_online_id, @staff_code, @session_id, @ip_address, @active", _
                                        New SqlParameter("@staff_online_id", row("staff_online_id")), _
                                        New SqlParameter("@staff_code", HttpContext.Current.Session("staff_code")), _
                                        New SqlParameter("@session_id", HttpContext.Current.Session("session_id")), _
                                        New SqlParameter("@ip_address", HttpContext.Current.Request.UserHostAddress), _
                                        New SqlParameter("@active", True))
                                Return 1
                            End If
                        End If
                    End If
                End If

                Return ""
            Catch ex As Exception
                Throw ex
            End Try
        End Function

        Public Shared Sub SetBreadCrump(ByVal tdBreadCrump As HtmlControl, ByVal ltrBreadCrump As Literal, ByVal sub_menu_code As Int32)
            Try
                ' Modified by Boonyarit Yingvoragan
                ' Modified date 20/10/2553
                Dim slash As Int32 = HttpContext.Current.Request.Url.PathAndQuery.LastIndexOf("/")
                Dim url = HttpContext.Current.Request.Url.PathAndQuery.Substring(slash + 1)
                Dim output = CodeMatrix.Utilities.CodeHelper.GetSingleObject( _
                    "exec USP_GetBreadCrumb @sub_menu_code", _
                    New SqlParameter("@sub_menu_code", sub_menu_code) _
                    ).ToString()

                If output = "" Then
                    tdBreadCrump.Visible = False
                Else
                    tdBreadCrump.Visible = True
                    ltrBreadCrump.Text = output
                End If
            Catch ex As Exception
                Throw ex
            End Try
        End Sub

        Public Shared Sub SetBreadCrump(ByVal tdBreadCrump As HtmlControl, ByVal ltrBreadCrump As Literal, ByVal text As String)
            Try
                If text = "" Then
                    tdBreadCrump.Visible = False
                Else
                    tdBreadCrump.Visible = True
                    ltrBreadCrump.Text = text
                End If
            Catch ex As Exception
                Throw ex
            End Try
        End Sub

    End Class
End Namespace
